using System;
using System.Data;
using System.Data.SqlClient;
using CavemanTools.Extensions;


namespace CavemanTools.Sql.Client
{
	/// <summary>
	/// Extension methods specific to SqlServer 2008+
	/// </summary>
	public static class SqlCommandExtensions
	{
		
		/// <summary>
		///  Creates a new SqlCommand populated with query text and parameters.
		/// You only have to execute it.
		/// </summary>
		/// <param name="cnx">SqlConnection object</param>
		/// <param name="query">Query string</param>
		/// <param name="param">Query parameters, null if missing</param>
		/// <returns></returns>
		public static SqlCommand CreateCommand(this SqlConnection cnx,string query, object param)
		{
			if (cnx == null) throw new ArgumentNullException("cnx");
			var cmd = cnx.CreateCommand();
			cmd.CommandText = query;
			if (param != null)
			{
				var p = param.ToDictionary();
				foreach (var kv in p)
				{
					CreateParameter(cmd.Parameters, kv.Key, kv.Value);				
				}
			}
			return cmd;
		}

		/// <summary>
		/// Creates SQlCommand with insert statement into table with object values as parameters
		/// </summary>
		/// <param name="cnx">SqlConnection object</param>
		/// <param name="table">Table name</param>
		/// <param name="param">Anonymous object</param>
		/// <returns></returns>
		public static SqlCommand CreateInsertQuery(this SqlConnection cnx,string table,object param)
		{
			return cnx.CreateCommand(param.GenerateInsertSql(table), param);
		}

		/// <summary>
		/// Sql Server 2008+ only!
		/// Creates SqlParameter.Supports automatically conversion of enums and date DateTime2
		/// </summary>
		/// <param name="param"></param>
		/// <param name="name"></param>
		/// <param name="value"></param>
		private static void CreateParameter(this SqlParameterCollection param, string name, object value)
		{
			if (param == null) throw new ArgumentNullException("param");
			if (value == null)
			{
				param.AddWithValue("@" + name, DBNull.Value);
				return;
			}
			var tp = value.GetType();
			if (tp.IsEnum)
			{
				value = value.ConvertTo(tp.BaseType);
			}
		
			
			var p = new SqlParameter("@" + name, value);
			if (tp.IsValueType && tp.Name == "DateTime")
			{
				var dt = (DateTime)value;
				if (dt < new DateTime(1753, 1, 1))
				{
					p.SqlDbType = SqlDbType.DateTime2;
				}
			}
			
			param.Add(p);
		}

		/// <summary>
		/// Executes a stored procedure command and gets the return value
		/// </summary>
		/// <typeparam name="T">TYpe of return value</typeparam>
		/// <param name="cmd"></param>
		/// <param name="sproc">Stored procedure name</param>
		/// <param name="param">Sproc params as anonymous object</param>
		/// <returns></returns>
		public static T ExecuteCommandWithReturn<T>(this SqlCommand cmd,string sproc,object param)
		{
			if (cmd == null) throw new ArgumentNullException("cmd");
			cmd.RecycleCommand(sproc,param);
			cmd.CommandType=CommandType.StoredProcedure;
			var ret=cmd.CreateParameter();
			ret.ParameterName = "returnValue";
			ret.Direction=ParameterDirection.ReturnValue;
			cmd.Parameters.Add(ret);

			cmd.ExecuteNonQuery();
			return cmd.GetSprocReturn<T>();
		}

		/// <summary>
		/// Gets the return of a sproc, from "returnValue" parameter
		/// </summary>
		/// <typeparam name="T">Type of return value</typeparam>
		/// <param name="cmd"></param>
		/// <returns></returns>
		public static T GetSprocReturn<T>(this SqlCommand cmd)
		{
			if (cmd == null) throw new ArgumentNullException("cmd");
			var param = cmd.Parameters["returnValue"];
			return param.Value.ConvertTo<T>();
		}

		/// <summary>
		/// Clears old command and params and sets new params and query
		/// </summary>
		/// <param name="cmd"></param>
		/// <param name="query">Sql string</param>
		/// <param name="param">Query parameters as anonymous object, null if missing</param>
		public static SqlCommand RecycleCommand(this SqlCommand cmd,string query, object param)
		{
			if (cmd == null) throw new ArgumentNullException("cmd");
			cmd.Parameters.Clear();
			cmd.CommandText = query;
			if (param != null)
			{
				var p = param.ToDictionary();

				foreach (var kv in p)
				{
					CreateParameter(cmd.Parameters, kv.Key, kv.Value);
				}
			}
			return cmd;
		}

		/// <summary>
		/// Executes a commandand returns the number of rows affected
		/// </summary>
		/// <param name="cnx">Connection string</param>
		/// <param name="query">Query</param>
		/// <param name="param">Anonymous object, param=value</param>
		/// <returns></returns>
		public static int QuickExecuteCommand(this string cnx,string query,object param)
		{
			if (string.IsNullOrEmpty(cnx)) throw new ArgumentNullException("cnx");
			using (var c= new SqlConnection(cnx))
			{
				using(var cmd=c.CreateCommand(query,param))
				{
					c.Open();
					return cmd.ExecuteNonQuery();					
				}
			}
		}

		
	}
}